import base64
import sqlite3
from datetime import datetime,timedelta
import pandas as pd

from db.db_work import MysqlLite
#将两个列表合并为相关的一个列表,两个列表一个是数据库字段名，一个是查询出来的数据
def get_data(lis1,lis2):
    result=[]
    for i in range(len(lis2)):
        data = {}
        for j in range(len(lis2[i])):
            data[lis1[j][1]] = lis2[i][j]
            if lis1[j][1] == 'avatar':
                data[lis1[j][1]] =base64.b64encode(data[lis1[j][1]]).decode('utf-8')
        result.append(data)
    return result
#获取已近完成的订单
def get_finish_orders(**kwargs):
    mysqlite = MysqlLite()
    result=[]
    if 'user_id' in kwargs:
        data = mysqlite.select_sql(f"select * from history_buy where user_id=?", (kwargs['user_id'],))
        for i in range(len(data)):
            a = mysqlite.select_sql(f"select shop_name,sellername from shops where ID=?", (data[i][3],))
            shop_name = a[0][0]
            sellername = a[0][1]
            date = datetime.strptime(data[i][1], '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')
            result.append([date, shop_name, data[i][2],sellername,data[i][0],data[i][6]])
    elif 'seller_id' in kwargs:
        data = mysqlite.select_sql(f"select ID,shop_name from shops where user_id=?", (kwargs['seller_id'],))
        for i in range(len(data)):
            tem = mysqlite.select_sql(f"select * from history_buy where shop_id=?", (data[i][0],))
            for j in range(len(tem)):
                date = datetime.strptime(tem[j][1], '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')
                user_name=mysqlite.select_sql(f"select username from user_info where ID=?", (tem[j][4],))[0][0]
                result.append([date, data[i][1], tem[j][2],user_name])  # 日期，商品名，商品数量,购买商品的人名
    result.sort(key=lambda x: x[0])
    return result
#获取未完成订单, 返回的数据为订单日期，商品名，商品数量，商家姓名，订单id
def get_no_finish_orders(**kwargs):
    mysqlite = MysqlLite()
    result=[]
    if 'user_id' in kwargs:
        data = mysqlite.select_sql(f"select * from orders where user_id=?", (kwargs['user_id'],))
        for i in range(len(data)):
            a = mysqlite.select_sql(f"select shop_name,sellername from shops where ID=?", (data[i][3],))
            shop_name = a[0][0]
            sellername = a[0][1]
            date = datetime.strptime(data[i][1], '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')
            result.append([date, shop_name, data[i][2],sellername,data[i][0]])
    elif 'seller_id' in kwargs:
        data=mysqlite.select_sql(f"select ID,shop_name from shops where user_id=?", (kwargs['seller_id'],))
        for i in range(len(data)):
            tem=mysqlite.select_sql(f"select * from orders where shop_id=?", (data[i][0],))
            for j in range(len(tem)):
                date = datetime.strptime(tem[j][1], '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')
                user_name = mysqlite.select_sql(f"select username from user_info where ID=?", (tem[j][4],))[0][0]
                result.append([date,data[i][1],tem[j][2],tem[j][0],user_name])  #日期，商品名，商品数量，订单号,购买商品的人名
    result.sort(key=lambda x: x[0])
    return result
#获取不在黑名单中的用户
def get_no_blacklist(data):
    result=[]
    for item in data:
        if item['blacklist'] == 0:
            result.append(item)
    return result
#获取在黑名单中的用户
def get_blacklist(data):
    result=[]
    for item in data:
        if item['blacklist'] == 1:
            result.append(item)
    return result
#获取所又历史购买数据
def get_date_sql():
    conn=sqlite3.connect('user.db')
    df = pd.read_sql("select * from history_buy",conn)
    conn.close()
    return df
#获取莫天mo产品的销售额
def get_sales_for_days(shop_id,days):
    df=get_date_sql()
    df=df[(df['shop_id'] == shop_id) & (df['dated_at'].str.startswith(days))]
    sum_=int(df['sum_'].sum())
    return sum_
#获取mo产品评分
def get_score(shop_id):
    df=get_date_sql()
    df=df[(df['comment'] != 0) & (df['shop_id'] == shop_id)]
    if df.shape[0]==0:
        return 0
    score=int(df['comment'].sum())/df.shape[0]
    return score
#获取mo商品总销售额
def get_Sales(shop_id):
    df=get_date_sql()
    df=df[df['shop_id'] == shop_id]
    sum=int(df['sum_'].sum())
    return sum
#获取mo商品各评论数量
def get_pl(shop_id,star):
    df=get_date_sql()
    df=df[(df['shop_id'] == shop_id) & (df['comment'] == star)]
    count=df.shape[0]
    return count